package com.comum.javahomework420242025.repository;
import com.comum.javahomework420242025.pojo.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "select * from user where account=:account and pass=:pass",nativeQuery = true)
    User findByAccountAndPassAndType(String account, String pass);
    @Query(value = "select user.id as id, user.name as name, " +
            "user.birthday as birthday, user.sex as sex, " +
            "user.phone as phone, user.comment as comment, " +
            "user.account as account, user.pass as pass, " +
            "user.type as type, user.areaid as areaid, " +
            "area.name as areaname from user join area on user.areaid=area.id where user.type='销售员工' and (" +
            "user.name like %:str% or sex like %:str% or " +
            "birthday like %:str% or area.name like %:str% or " +
            "type like %:str% or phone like %:str% or user.comment like %:str%)",nativeQuery = true)
    Page<User> selectByStr(String str, Pageable pageable);
    @Query(value = "select * from user where :sql",nativeQuery = true)
    Page<User> selectByDepartmentAndAreaidAndStr(String sql, Pageable pageable);


    @Query(value = "SELECT * FROM user WHERE type = :type", nativeQuery = true)
    Page<User> findALLByType(@Param("type") String type, Pageable pageable);

      @Query("select u from user u where u.areaid=:id")
    List<User> findByAreaId(String id);


    @Query("SELECT u FROM user u WHERE u.type = :type AND u.id NOT IN " +
            "(SELECT t.user.id FROM Traceuser t WHERE t.chance.chanceid = :chanceId)")
    Page<User> getAllAreaMasterNotChoose(@Param("type") String type, @Param("chanceId") String chanceId, Pageable pageable);

    @Query("SELECT u from user u where u.type = :type")
    List<User> findUserBYTyoe(@Param("type") String type);
}
